--- Display period statuses select glstat.period_name, glstat.closing_status, appdesc.application_short_name FROM GL_PERIOD_STATUSES glstat, fnd_application appdesc where set_of_Books_id = 85 and closing_status = 'O' and glstat.application_id = appdesc.application_id; --Find the flex value sets associated with the Chart of Accounts associated with the Set of Books (11i)/Ledger (R12) --For 11i SELECT glsob.name, idfs.id_flex_num chart_of_accounts_id, idfs.segment_num, idfs.flex_value_set_id, idfs.application_column_name, idfs.segment_name FROM gl_sets_of_books glsob, fnd_id_flex_segments idfs, fnd_flex_value_sets fvs WHERE glsob.chart_of_accounts_id = idfs.id_flex_num AND fvs.flex_value_set_id = idfs.flex_value_set_id AND glsob.set_of_books_id = &SoBID AND idfs.application_id = 101 AND idfs.id_flex_code = 'GL#' ORDER BY idfs.segment_num ASC; --For R12 SELECT led.name, idfs.id_flex_num chart_of_accounts_id, idfs.segment_num, idfs.flex_value_set_id, idfs.application_column_name, idfs.segment_name FROM gl_ledgers led, fnd_id_flex_segments idfs, fnd_flex_value_sets fvs WHERE led.chart_of_accounts_id = idfs.id_flex_num AND fvs.flex_value_set_id = idfs.flex_value_set_id AND led.ledger_id = &LedgerId AND idfs.application_id = 101 AND idfs.id_flex_code = 'GL#' ORDER BY idfs.segment_num ASC; ---Displays GL code with parent & rollup details SELECT fffv.flex_value ,fffv.flex_value_meaning ,fffv.description ,fffv.enabled_flag ,fffv.summary_flag ,SUBSTR(compiled_Value_attributes,1,1) Budgeting ,SUBSTR(compiled_Value_attributes,3,1) Posting ,(SELECT ffhv.hierarchy_code FROM fnd_flex_hierarchies_vl ffhv WHERE ffhv.hierarchy_id = fffv.structured_hierarchy_level) rollup_group ,(SELECT DISTINCT parent_flex_value FROM fnd_flex_value_children_v ffvc WHERE ffvc.flex_value = fffv.flex_value AND ffvc.flex_value_set_id = 1005004 AND rownum = 1) parent_value_1 ,(SELECT DISTINCT parent_flex_value FROM fnd_flex_value_children_v ffvc WHERE ffvc.flex_value = fffv.flex_value AND ffvc.flex_value_set_id = 1005004 AND rownum = 2) parent_value_2 ,fffv.start_date_active ,fffv.end_date_active FROM fnd_flex_values_vl fffv WHERE fffv.flex_value_set_id = 1005004 AND fffv.enabled_flag = 'Y' AND (TRUNC(fffv.end_date_active) >= TRUNC(SYSDATE) OR fffv.end_date_active IS NULL) ---Query for displaying journal entries Select a.*,e.name,c.user_je_source_name,f.user_je_category_name, d.segment1,d.segment2,d.segment3,d.segment4,d.segment5,d.segment6,d.segment7,d.segment8,d.segment9 from gl_je_lines a , gl_je_headers b, gl_je_sources_tl c, gl_code_combinations d, gl_je_batches e, gl_je_categories_tl f where a.je_header_id = b.je_header_id and b.je_source = c.je_source_name and b.je_batch_id = e.je_batch_id and b.je_category = f.je_category_name and a.code_combination_id = d.code_combination_id and c.user_je_source_name = 'Projects Equipment Supply' and f.user_je_category_name = 'Revenue' and -- a.status = 'P' and a.set_of_books_id = 85 and -- a.reference_10 in ('WRITEOFF','ROUNDING'); a.period_name in('Apr-06','May-06','Jun-06','Jul-06','Aug-06','Sep-06','Oct-06','Nov-06','Dec-06','Jan-07','Feb-07','Mar-07') and -- a.effective_date between '01-Apr-2006' and '30-Jun-2006' and -- e.je_batch_id = 237875 -- d.segment1 = '704' and -- d.segment7 between '23901' and '23901' and a.reference_2 = '2046009' /*and (accounted_dr=8461.54 or accounted_Cr=8461.54) a.description like '%0607/237%'*/; /*group by e.name,c.user_je_source_name,f.user_je_category_name,d.segment7,d.segment1; */ ---RECONCILIATION BETWEEN INVENTORY AND GL SELECT gl.period_name, gl.gl_batch_id, gl.org_code, SUM (gl.gl_net) , mtl.gl_batch_id inv_gl_batch_id, mtl.org_code, SUM (mtl.mtl_net) , SUM (gl.gl_net - mtl.mtl_net) net FROM (SELECT /*+ ordered use_nl(jel jeh jeb cat src) index(jel gl_je_lines_n1) index(jeh gl_je_headers_u1) index(jeb gl_je_batches_u1) index(cat gl_je_categories_u1) index(src gl_je_sources_u1) */ SUBSTR (jeb.NAME, 1, INSTR (jeb.NAME, ' ')) gl_batch_id , LTRIM (SUBSTR (jeb.NAME, INSTR (jeb.NAME, ' '), 4)) org_code , NVL ((SUM (NVL (jel.accounted_dr, 0)) - SUM (NVL (jel.accounted_cr, 0))) , 0 ) gl_net , cc.code_combination_id, per.period_name FROM gl_code_combinations_kfv cc , gl_je_lines jel , gl_je_headers jeh , gl_je_batches jeb , gl_je_categories cat , gl_je_sources src , gl_period_statuses per WHERE cc.concatenated_segments = '101-DI003-11211106' AND src.je_source_name = 'Inventory' AND cat.user_je_category_name = ('MTL') AND per.period_name = 'NOV-07' AND jeh.period_name = per.period_name AND cc.chart_of_accounts_id = &coa_id AND jel.code_combination_id = cc.code_combination_id AND jel.status || '' = 'P' AND (jel.entered_dr != 0 OR jel.entered_cr != 0) AND jeh.je_header_id = jel.je_header_id AND jeh.actual_flag = 'A' AND jeh.currency_code = 'ZAR' AND jeb.je_batch_id = jeh.je_batch_id AND jeb.set_of_books_id = &sob_id AND jeb.average_journal_flag = 'N' AND src.je_source_name = jeh.je_source AND cat.je_category_name = jeh.je_category AND jeh.reversed_je_header_id IS NULL AND per.application_id = 101 AND per.set_of_books_id = jeb.set_of_books_id AND LTRIM (SUBSTR (jeb.NAME, INSTR (jeb.NAME, ' '), 4)) = &inv_org_code GROUP BY cc.code_combination_id , per.period_name , SUBSTR (jeb.NAME, 1, INSTR (jeb.NAME, ' ')) , LTRIM (SUBSTR (jeb.NAME, INSTR (jeb.NAME, ' '), 4)) ORDER BY LTRIM (SUBSTR (jeb.NAME, INSTR (jeb.NAME, ' '), 4)) , SUBSTR (jeb.NAME, 1, INSTR (jeb.NAME, ' '))) gl , (SELECT gl_batch_id gl_batch_id, organization_code org_code , SUM (NVL (base_transaction_value, 0)) mtl_net, reference_account mtl_acct FROM mtl_transaction_accounts a, mtl_parameters b, gl_code_combinations_kfv gcc WHERE a.organization_id = b.organization_id AND b.organization_code = &inv_org_code AND a.reference_account = gcc.code_combination_id AND gcc.concatenated_segments = '101-DI003-11211106' GROUP BY gl_batch_id, organization_code, reference_account) mtl WHERE gl.gl_batch_id = mtl.gl_batch_id AND gl.code_combination_id = mtl.mtl_acct AND gl.org_code = mtl.org_code GROUP BY gl.period_name, gl.gl_batch_id, gl.org_code, mtl.gl_batch_id, mtl.org_code ORDER BY mtl.org_code, gl.period_name; --Display journal details trfd. from Inventory sub-ledger SELECT gjh.period_name "Period name" ,gjb.name "Batch name" ,gjjlv.header_name "Journal entry" ,gjjlv.je_source "Source" ,glcc.concatenated_segments "Accounts" ,mmt.subinventory_code "Subinventory" ,glcc3.segment4 "Costcenter" ,gjjlv.line_entered_dr "Entered debit" ,gjjlv.line_entered_cr "Entered credit" ,gjjlv.line_accounted_dr "Accounted debit" ,gjjlv.line_accounted_cr "Accounted credit" ,gjjlv.currency_code "Currency" ,mtt.transaction_type_name "Transaction type" ,TO_CHAR(mta.transaction_id)"Transaction_number" ,mta.transaction_date "Transaction_date" ,msi.segment1 "Reference" FROM apps.gl_je_journal_lines_v gjjlv, gl_je_lines gje, mtl_transaction_accounts mta, mtl_material_transactions mmt, mtl_system_items_b msi, gl_je_headers gjh, gl_je_batches gjb, apps.gl_code_combinations_kfv glcc, apps.gl_code_combinations_kfv glcc2, mtl_secondary_inventories msin, mtl_transaction_types mtt, MTL_SECONDARY_INVENTORIES cost, gl_code_combinations glcc3 WHERE gjjlv.period_name BETWEEN 'NOV-2008' AND 'DEC-2008' AND gje.code_combination_id = gje.code_combination_id AND gjjlv.line_je_line_num = gje.je_line_num AND gl_sl_link_table = 'MTA' AND gjjlv.je_header_id = gje.je_header_id AND mmt.inventory_item_id = msi.inventory_item_id AND gje.je_header_id = gjh.je_header_id AND gjh.je_batch_id = gjb.je_batch_id AND mmt.organization_id = msi.organization_id AND mmt.organization_id = msin.organization_id AND mmt.subinventory_code= msin.secondary_inventory_name AND mta.gl_sl_link_id= gje.gl_sl_link_id AND mta.reference_account = glcc.code_combination_id AND msin.expense_account = glcc2.code_combination_id AND mmt.transaction_id = mta.transaction_id AND mtt.transaction_type_id = mmt.transaction_type_id AND cost.organization_id(+) = mmt.organization_id AND cost.secondary_inventory_name(+) = mmt.subinventory_code AND glcc3.code_combination_id(+) = cost.expense_account